package jdbc;

import java.io.IOException;//模板
import java.sql.ResultSet;//模板//模板
import java.sql.SQLException;//模板

import javax.servlet.annotation.WebServlet;//模板
import javax.servlet.http.HttpServlet;//模板
import javax.servlet.http.HttpServletRequest;//模板
import javax.servlet.http.HttpServletResponse;//模板

@WebServlet("/src/showcourse")
public class showcourse extends HttpServlet{//模板
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;//模板

	@Override //模板
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException {//模板
		//设置请求头
		response.setHeader("Access-Control-Allow-Origin", "*");//模板
	    response.setHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");//模板
	    response.setHeader("Access-Control-Allow-Headers", "Content-Type");//模板
	    //设置编码格式
	    request.setCharacterEncoding("UTF-8");//模板
	    response.setCharacterEncoding("UTF-8");//模板
		
	    String current_page = request.getParameter("current_page1");
	    String recordsperpage = request.getParameter("recordsperpage1");
	    String student_id = request.getParameter("student_id");
	    String search = request.getParameter("search");
	    int currentPage = Integer.parseInt(current_page);
	    int recordsPerpage = Integer.parseInt(recordsperpage);
	    
	    hello a = new hello();//模板
		try {//模板
			ResultSet b;
			if (search!=null) {
				
				b = a.doselect("SELECT c.course_name, t.teacher_name, c.course_time,c.course_id,"
						+ "CASE WHEN sc.student_id IS NOT NULL THEN 1 ELSE 0 END AS selected "
						+ "from course c "
						+ "INNER JOIN teacher t ON c.course_teacher = t.teacher_id "
						+ "LEFT JOIN sc ON c.course_id = sc.course_id AND sc.student_id = ? "
						+ "where (course_name like ? or teacher_name like ?)",student_id, "%"+search+"%","%"+search+"%");
			    int rowCount = 0;
			    if (b.last()) {
			        rowCount = b.getRow();
			        b.beforeFirst(); // 将指针移回起始位置
			        response.getWriter().write(String.valueOf(rowCount)+";");
			    }
				b = a.doselect("SELECT c.course_name, t.teacher_name, c.course_time,c.course_id,"
						+ "CASE WHEN sc.student_id IS NOT NULL THEN 1 ELSE 0 END AS selected "
						+ "from course c "
						+ "INNER JOIN teacher t ON c.course_teacher = t.teacher_id "
						+ "LEFT JOIN sc ON c.course_id = sc.course_id AND sc.student_id = ? "
						+ "where (course_name like ? or teacher_name like ?)"
						+ "LIMIT ?, ?",student_id, "%"+search+"%","%"+search+"%", (currentPage-1)*recordsPerpage, recordsPerpage);
			}
			else {
				b = a.doselect("SELECT c.course_name, t.teacher_name, c.course_time,c.course_id,"
						+ "       CASE WHEN sc.student_id IS NOT NULL THEN 1 ELSE 0 END AS selected "
						+ "FROM course c "
						+ "INNER JOIN teacher t ON c.course_teacher = t.teacher_id "
						+ "LEFT JOIN sc ON c.course_id = sc.course_id AND sc.student_id = ?",student_id);
			    int rowCount = 0;
			    if (b.last()) {
			        rowCount = b.getRow();
			        b.beforeFirst(); // 将指针移回起始位置
			        response.getWriter().write(String.valueOf(rowCount)+";");
			    }
				b = a.doselect("SELECT c.course_name, t.teacher_name, c.course_time,c.course_id,"
						+ "       CASE WHEN sc.student_id IS NOT NULL THEN 1 ELSE 0 END AS selected "
						+ "FROM course c "
						+ "INNER JOIN teacher t ON c.course_teacher = t.teacher_id "
						+ "LEFT JOIN sc ON c.course_id = sc.course_id AND sc.student_id = ? LIMIT ?, ?",student_id,(currentPage-1)*recordsPerpage, recordsPerpage);
			}
			while(b.next()) {//b代表查询结果，查询之后的那个表，next（）函数就是往下一行，逐行扫描
				response.getWriter().write(b.getString("course_name")+";");//这里表示的是往返回的那个response里面添加字符串，修改write括号后的内容即可
				response.getWriter().write(b.getString("teacher_name")+";");
				response.getWriter().write(b.getString("course_time")+";");
				response.getWriter().write(b.getString("course_id")+";");
				response.getWriter().write(b.getString("selected")+";");
			}//模板
			b.close();
		
		} catch (SQLException e) {//模板
			e.printStackTrace();//模板
		}//模板
	}//模板
}//模板
